/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.controller;

import java.io.File;

import org.springframework.util.StringUtils;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: Test.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-10-22 下午8:51:56
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

public class Test {

	public static void main(String[] args) throws Exception {
		File file = new File("C:\\template.xls");
		if(!file.exists()) {
			file.createNewFile();
		}
		file.setWritable(true);
		Workbook workbook = Workbook.getWorkbook(file);
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(new File("C:\\备份.xls"), workbook);
		WritableSheet sheet = writableWorkbook.getSheet(0);
		
		WritableFont font = new WritableFont(WritableFont.ARIAL, 12,
			     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
		WritableCellFormat wcf = new WritableCellFormat(font); //单元格定义
		wcf.setBackground(Colour.YELLOW);
		wcf.setAlignment(Alignment.CENTRE);
		int rows = sheet.getRows();
		String result = ""; //返回结果
		String companyName = ""; //公司名称
		String departmentName = ""; //部门名称
		String headshipName =  ""; //岗位名称 
		String userName = ""; //用户姓名
		String mobile = ""; //用户手机号码
		for(int r = 1; r < rows; r++) {
			companyName = sheet.getCell(1, r).getContents().trim();
			departmentName = sheet.getCell(2, r).getContents().trim();
			headshipName = sheet.getCell(3, r).getContents().trim();
			userName = sheet.getCell(4, r).getContents().trim();
			mobile = sheet.getCell(5, r).getContents().trim();
			if(!StringUtils.hasText(companyName)) {
				result += "第" + (r + 1) + "行公司名称为空，导入失败\n";
				break;
			}
			if(!StringUtils.hasText(departmentName)) {
				result += "第" + (r + 1) + "行部门名称为空，导入失败\n";
				break;
			}
			if(!StringUtils.hasText(headshipName)) {
				result += "第" + (r + 1) + "行岗位名称为空，导入失败\n";
				break;
			}
			if(!StringUtils.hasText(userName)) {
				result += "第" + (r + 1) + "行姓名为空，导入失败\n";
				break;
			}
			if("".equals(mobile) || mobile.length() < 11 || mobile.length() > 11) {
				result += "第" + (r + 1) + "行电话号码有误，导入失败\n";
				break;
			} else {
				if("15956909259".equals(mobile)) {
					result += "第" + (r + 1) + "行手机号码在系统中已存在，导入失败\n";
					break;
				}
			}
		}
		
		System.out.println("================================================");
		System.out.println("result : " + result);
		System.out.println("================================================");
		
		writableWorkbook.write();
		writableWorkbook.close();
	}
	
	private static Label generateLabel(int row, int col, String content, WritableCellFormat wcf) {
		return new Label(row, col, content, wcf);
	}
	
	public static void test() throws Exception {
		File file = new File("C:\\template.xls");
		if(!file.exists()) {
			file.createNewFile();
		}
		file.setWritable(true);
		Workbook workbook = Workbook.getWorkbook(file);
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(new File("C:\\备份.xls"), workbook);
		WritableSheet sheet = writableWorkbook.getSheet(0);
		
		WritableFont font = new WritableFont(WritableFont.ARIAL, 12,
			     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
		WritableCellFormat wcf = new WritableCellFormat(font); //单元格定义
		wcf.setBackground(Colour.YELLOW);
		wcf.setAlignment(Alignment.CENTRE);
		int rows = sheet.getRows();
		int cols = sheet.getColumns();
		System.out.println("rows : " + rows);
		System.out.println("cols : " + cols);
		for(int i = 0; i < rows; i++) {
			for(int j = 0; j < cols; j++) {
				if("15956909259".equals(sheet.getCell(j, i).getContents().trim())) {
					sheet.addCell(generateLabel(j, i, sheet.getCell(j, i).getContents().trim(), wcf));
				}
				System.out.print(sheet.getCell(j, i).getContents().trim() + "\t");
			}
			System.out.println();
		}
		writableWorkbook.write();
		writableWorkbook.close();
	}
}